package veilingsite.dao.nl;

import java.io.UnsupportedEncodingException;
import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Formatter;

import veilingsite.beans.nl.UserBean;
import veilingsite.database.nl.DBmanager;

public class UserDao {
	private String sqlread   	   = "SELECT * FROM users";
	private String sqlGetU   	   = "SELECT * FROM users WHERE ID = ?";
	private String sqlGetN   	   = "SELECT * FROM users WHERE username = ?";
	private String sqlLogin		   = "SELECT * FROM users WHERE username = ? AND password = ?";
	private String sqlCreate   	   = "INSERT INTO users(username, password, name) VALUES(?, ?, ?)";
	private String sqlDelete   	   = "DELETE FROM users WHERE ID = ?";
	private String sqlCreateGebruikerTable = "CREATE TABLE users (ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY" +
			"(START WITH 1, INCREMENT BY 1), username VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL, " +
			"name VARCHAR(50) NOT NULL)";
	
	private Connection conn;
	private Statement stmt;
	private PreparedStatement psRead   = null;
	private PreparedStatement psGetU   = null;
	private PreparedStatement psGetN   = null;
	private PreparedStatement psLogin  = null;
	private PreparedStatement psCreate = null;
	private PreparedStatement psDelete = null;
	
	public UserDao(DBmanager db){
		conn = db.getConnection();

	}
	
	public void init(){
		try {
			this.psRead   = conn.prepareStatement(sqlread);
			this.psGetU   = conn.prepareStatement(sqlGetU);
			this.psGetN   = conn.prepareStatement(sqlGetN);
			this.psLogin  = conn.prepareStatement(sqlLogin);
			this.psCreate = conn.prepareStatement(sqlCreate);
			this.psDelete = conn.prepareStatement(sqlDelete);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public ArrayList<UserBean> getUsers(){
		ArrayList<UserBean> users = new ArrayList<UserBean>();
		try{
			ResultSet rs = psRead.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String username = rs.getString(2);
				String name = rs.getString(4);
				
				UserBean user = new UserBean();
				user.setAll(id, username, name, false);
				users.add(user);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return users;
	}	
	
	public UserBean getUserByID(int getUserID){
		UserBean user = null;
		try{
			psGetU.clearParameters() ;
			psGetU.setInt(1, getUserID);
			ResultSet rs = psGetU.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String username = rs.getString(2);
				String password = rs.getString(3);
				String name= rs.getString(4);
				
				user = new UserBean();
				user.setAll(id, username, name, false);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return user;
	}
	
	public UserBean getUserByUsername(String getUsername){
		UserBean user = null;
		try{
			psGetN.clearParameters() ;
			psGetN.setString(1, getUsername);
			ResultSet rs = psGetN.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String username = rs.getString(2);
				String name = rs.getString(4);
				user = new UserBean();
				user.setAll(id, username, name, false);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return user;
	}
	
	private String getMD5HashVal( String strToBeEncrypted ) {
		String encryptedString = null;  
		byte[] bytesToBeEncrypted;  
		try {  

			bytesToBeEncrypted = strToBeEncrypted.getBytes("ISO-8859-1");
			
			MessageDigest md = MessageDigest.getInstance("MD5");  
			byte[] theDigest = md.digest(bytesToBeEncrypted);  

			// converteer bytes naar heximale digets 
			Formatter formatter = new Formatter();  
			for (byte b : theDigest) {  
				formatter.format("%02x", b);  
			}  
			encryptedString = formatter.toString().toLowerCase();  

		} catch (NoSuchAlgorithmException e) {  
			e.printStackTrace();  
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}  
		return encryptedString;  
	}  
	
	public UserBean userLogin( String username, String password ) {
		UserBean user = null;
		String hashedPassword = getMD5HashVal( password );
		try {
			psLogin.clearParameters();
			psLogin.setString( 1, username );
			psLogin.setString( 2, hashedPassword ); 
			ResultSet rs = psLogin.executeQuery();
			while( rs.next() ) {
				int id = rs.getInt(1);
				String name = rs.getString(4);
				user = new UserBean();
				user.setAll(id, username, name, true);
			}
			rs.close();
		} catch( SQLException se ) {
			se.printStackTrace();
		}
		return user;
	}
	
	public boolean createKlant(String username, String password, String name) {
		boolean succeeded = false;
		try {
			psCreate.clearParameters();
			psCreate.setString( 1, username );
			psCreate.setString( 2, getMD5HashVal(password) );
			psCreate.setString( 3, name );
			if( psCreate.executeUpdate() != 0 ) {
				succeeded = true;
			}
		} catch( SQLException se ) {
			se.printStackTrace();
		}
		return succeeded;
	}
	
	public boolean deleteUser(int id) {
		boolean succeeded = false;
		try {
			psDelete.clearParameters();
			psDelete.setInt( 1, id );
			if( psDelete.executeUpdate() != 0 ) {
				succeeded = true;
			}
		} catch( SQLException se ) {
			se.printStackTrace();
		}
		return succeeded;
	}

	/**
	 * Creates the user table if it doesn't exist yet
	 */
	public void createTablesIfNeeded(){
		try {
			if(!checkIfUserTableExists()){
				stmt = conn.createStatement();
				stmt.execute(sqlCreateGebruikerTable);	
			}
			
		} catch (SQLException e){
			e.printStackTrace();
		}
	}
	
	public boolean checkIfUserTableExists(){
		try {
			DatabaseMetaData dbd = conn.getMetaData();
			ResultSet rs = dbd.getTables(null, null, null, new String[]{"TABLE"});
			while (rs.next()){
				if(rs.getString("TABLE_NAME").equalsIgnoreCase("users")){
					return true;
				}
			}
			
		} catch (SQLException e) {
			return false;
		}
		
		return false;
	}

}
